﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MySql.Data.MySqlClient;
using YmtAuth.Domain;
using YmtAuth.Domain.Repository;
using YmtSystem.CrossCutting;
using YmtAuth.Dto;

namespace YmtAuth.Repository.MySQL
{
    public class SecurityQuestionRepository : MySQLRepository<SecurityQuestion>, ISecurityQuestionRepository
    {
        public SecurityQuestionRepository()
            : base(ConfigurationManager.AppSettings["SecurityQuestion_MySQLConnString"], "securityquestion")
        {

        }

        /// <summary>
        /// 获取问题列表
        /// </summary>
        /// <returns></returns>
        public ResponseData<List<SecurityQuestion>> GetSecurityQuestionList()
        {
            ResponseData<List<SecurityQuestion>> response = null;

            try
            {
                string selectSQL = "select QuestionId, QuestionContent, QuestionTip from securityquestion where IsDelete = 0";
                DynamicParameters selectPar = this.Parameters.Build();

                List<SecurityQuestion> securityQuestionList = null;

                using (var conn = new MySqlConnection(ConfigurationManager.AppSettings["SecurityQuestion_MySQLConnString"]))
                {
                    securityQuestionList = conn.Query<SecurityQuestion>(selectSQL, selectPar, commandType: CommandType.Text).ToList();
                }

                response = ResponseData<List<SecurityQuestion>>.Create(securityQuestionList, true, "Success", SecurityQuestionResponseCode.Success);
            }
            catch (Exception ex)
            {
                response = ResponseData<List<SecurityQuestion>>.Create(null, false, "获取安全问题列表出现异常, " + ex.Message, SecurityQuestionResponseCode.Exception);
            }

            return response;
        }

        /// <summary>
        /// 设置安全问题
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public ResponseData<bool> SetSecurityQuestion(int userId, List<SecurityQuestionAnswer> list)
        {
            ResponseData<bool> response = null;

            try
            {
                bool returnVal = false;
                int affectedRows = 0;

                if (list != null && list.Any())
                {
                    var updateSQL = "UPDATE usersecurityquestion SET IsDelete = 1, LastUpdateTime = CURRENT_TIMESTAMP WHERE IsDelete = 0 AND UserId = " + userId;
                    var insertSql = "INSERT INTO usersecurityquestion (UserId, QuestionId, QuestionAnswer, IsDelete, CreateTime) VALUES (@userid, @questionid, @questionanswer, @isdelete, @createtime)";

                    using (var conn = new MySqlConnection(ConfigurationManager.AppSettings["SecurityQuestion_MySQLConnString"]))
                    {
                        affectedRows = conn.Execute(updateSQL, null, commandType: CommandType.Text);
                        YmatouLoggingService.Debug("[SetSecurityQuestion] update original user({0}) security questions: {1} rows", userId, affectedRows);

                        affectedRows = 0;

                        foreach (SecurityQuestionAnswer answer in list)
                        {
                            var insertPar = this.Parameters.AddInt("@userid", userId)
                            .AddInt("@questionid", answer.QuestionId)
                            .AddString("@questionanswer", answer.QuestionAnswer)
                            .AddByte("@isdelete", (byte)0)
                            .AddDateTime("@createtime", DateTime.Now).Build();

                            conn.Execute(insertSql, insertPar, commandType: CommandType.Text);
                            YmatouLoggingService.Debug("[SetSecurityQuestion] insert user({0}) security question, questionid: {1}", userId, answer.QuestionId);
                            affectedRows++;
                        }
                    }

                    returnVal = affectedRows == list.Count();
                }

                if (returnVal)
                {
                    response = ResponseData<bool>.Create(true, true, "success", SecurityQuestionResponseCode.Success);
                }
                else
                {
                    response = ResponseData<bool>.Create(false, true, "设置安全问题失败", SetSecurityQuestionResponseCode.SaveToDBError);
                }
            }
            catch (Exception ex)
            {
                response = ResponseData<bool>.Create(false, false, "设置安全问题出现异常, " + ex.Message, SecurityQuestionResponseCode.Exception);
            }

            return response;
        }

        /// <summary>
        /// 获取已设置的安全问题（单个，验证）
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="list"></param>
        /// <param name="nextVerifyDayCount"></param>
        /// <returns></returns>
        public ResponseData<UserSecurityQuestion> GetSecurityQuestion(int userId, List<UserSecurityQuestion> list, int nextVerifyDayCount)
        {
            ResponseData<UserSecurityQuestion> response = null;

            try
            {
                if (list.Any())
                {
                    List<int> userQuestionIdList = new List<int>();

                    var selectSQL = "SELECT UserQuestionId FROM verifysecurityquestionlog where UserId = @userid AND LastUpdateTime > @periodBeginTime AND IsDelete = 0";

                    var par = this.Parameters.AddInt("@userid", userId).AddDateTime("@periodBeginTime", GetPeriodBeginTime(nextVerifyDayCount)).Build();

                    using (var conn = new MySqlConnection(ConfigurationManager.AppSettings["SecurityQuestion_MySQLConnString"]))
                    {
                        var _userQuestionIdList = conn.Query<int>(selectSQL, par, commandType: CommandType.Text);

                        if (_userQuestionIdList != null && _userQuestionIdList.Any())
                        {
                            userQuestionIdList = _userQuestionIdList.ToList();
                        }
                    }

                    if (list.Any(r => !userQuestionIdList.Contains(r.UserQuestionId)))
                    {
                        UserSecurityQuestion userSecurityQuestion = list.First(r => !userQuestionIdList.Contains(r.UserQuestionId));
                        response = ResponseData<UserSecurityQuestion>.Create(userSecurityQuestion, true, "success", SecurityQuestionResponseCode.Success);
                    }
                    else
                    {
                        response = ResponseData<UserSecurityQuestion>.Create(null, true, "无可用的用户安全问题", GetUserSecurityQuestionResponseCode.CanNotFindAvailableUserSecurityQuestion);
                    }
                }
                else
                {
                    response = ResponseData<UserSecurityQuestion>.Create(null, true, "此用户未设置过安全问题", SecurityQuestionResponseCode.UserHasNotSetSecurityQuestion);
                }
            }
            catch (Exception ex)
            {
                response = ResponseData<UserSecurityQuestion>.Create(null, false, "获取用户安全问题出现异常, " + ex.Message, SecurityQuestionResponseCode.Exception);
            }

            return response;
        }

        /// <summary>
        /// 验证安全问题
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="userQuestionId"></param>
        /// <param name="answer"></param>
        /// <returns></returns>
        public ResponseData<bool> VerifySecurityQuestion(int userId, int userQuestionId, string answer)
        {
            ResponseData<bool> response = null;

            try
            {
                List<UserSecurityQuestion> list = GetUserSecurityQuestion(userId);

                if (list.Any())
                {
                    if (list.Any(r => r.UserQuestionId == userQuestionId))
                    {
                        
                        if (list.Any(r => r.UserQuestionId == userQuestionId && string.Equals(answer, r.QuestionAnswer, StringComparison.InvariantCultureIgnoreCase)))
                        {
                            response = ResponseData<bool>.Create(true, true, "success", SecurityQuestionResponseCode.Success);
                        }
                        else
                        {
                            response = ResponseData<bool>.Create(false, true, "用户的安全问题答案错误, " + answer, VerifySecurityQuestionResponseCode.WrongAnswer);
                        }
                    }
                    else
                    {
                        response = ResponseData<bool>.Create(false, true, "用户没有设置过此安全问题", VerifySecurityQuestionResponseCode.WrongUserQuestionId);
                    }
                }
                else
                {
                    response = ResponseData<bool>.Create(false, true, "此用户未设置过安全问题", SecurityQuestionResponseCode.UserHasNotSetSecurityQuestion);
                }
            }
            catch (Exception ex)
            {
                response = ResponseData<bool>.Create(false, false, "验证安全问题出现异常, " + ex.Message, SecurityQuestionResponseCode.Exception);
            }

            return response;
        }

        /// <summary>
        /// 获取用户安全问题列表
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<UserSecurityQuestion> GetUserSecurityQuestion(int userId)
        {
            List<UserSecurityQuestion> list = new List<UserSecurityQuestion>();

            try
            {
                var selectSQL = "SELECT t1.UserQuestionId, t2.QuestionContent, t2.QuestionTip, t1.QuestionAnswer FROM usersecurityquestion t1 join securityquestion t2 on t1.QuestionId = t2.QuestionId WHERE t1.UserId = @userid AND t1.IsDelete = 0 AND t2.IsDelete = 0";
                var par = this.Parameters.AddInt("@userid", userId).Build();

                using (var conn = new MySqlConnection(ConfigurationManager.AppSettings["SecurityQuestion_MySQLConnString"]))
                {
                    var questionList = conn.Query<UserSecurityQuestion>(selectSQL, par, commandType: CommandType.Text);

                    if (questionList != null && questionList.Any())
                    {
                        list = questionList.ToList();
                    }
                }

                YmatouLoggingService.Debug("[GetUserSecurityQuestion] user({0}) security question count: {1} rows", userId, list.Count());
            }
            catch (Exception ex)
            {
                string errMsg = string.Format("获取用户安全问题列表异常, GetUserSecurityQuestion error, userId: {0}, ex: {1}", userId, ex.Message);
                YmatouLoggingService.Debug(errMsg);
            }

            return list;
        }

        /// <summary>
        /// 获取用户当前的安全问题验证状态
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public ResponseData<UserSecurityQuestionVerifyStatus> GetCurrentVerifyStatus(int userId, int nextVerifyDayCount)
        {
            ResponseData<UserSecurityQuestionVerifyStatus> response = null;

            var selectSQL = "SELECT count(1) VerifyCount, max(LastUpdateTime) LatestVerifyTime FROM verifysecurityquestionlog WHERE UserId = @userid AND LastUpdateTime > @periodBeginTime AND IsDelete = 0 AND VerifyCode <> '200'";

            var par = this.Parameters.AddInt("@userid", userId).AddDateTime("@periodBeginTime", GetPeriodBeginTime(nextVerifyDayCount)).Build();

            UserSecurityQuestionVerifyStatus verifyStatus = new UserSecurityQuestionVerifyStatus();
            try
            {
                using (var conn = new MySqlConnection(ConfigurationManager.AppSettings["SecurityQuestion_MySQLConnString"]))
                {
                    verifyStatus = conn.Query<UserSecurityQuestionVerifyStatus>(selectSQL, par, commandType: CommandType.Text).First();
                }

                response = ResponseData<UserSecurityQuestionVerifyStatus>.Create(verifyStatus, true, "success", "200");
            }
            catch (Exception ex)
            {
                string errMsg = string.Format("获取用户当前的安全问题验证状态异常 userId: {0}, ex: {1}", userId, ex.Message);
                response = ResponseData<UserSecurityQuestionVerifyStatus>.Create(verifyStatus, false, errMsg, "500");
                YmatouLoggingService.Debug(errMsg);
            }

            return response;
        }

        /// <summary>
        /// 获取当前时间的周期开始时间
        /// </summary>
        /// <param name="nextVerifyDayCount"></param>
        /// <returns></returns>
        private DateTime GetPeriodBeginTime(int nextVerifyDayCount)
        {
            DateTime today = DateTime.Now;
            return new DateTime(today.Year, today.Month, today.Day, 0, 0, 0).AddDays(-(nextVerifyDayCount - 1));
        }

        /// <summary>
        /// 身份认证成功，安全问题尝试验证次数清零
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="nextVerifyDayCount"></param>
        /// <returns></returns>
        public ResponseData<bool> ResetUserVerifyCount(int userId, int nextVerifyDayCount)
        {
            ResponseData<bool> res = null;

            try
            {
                //验证成功 尝试验证次数清零
                using (var conn = new MySqlConnection(ConfigurationManager.AppSettings["SecurityQuestion_MySQLConnString"]))
                {
                    string updateSql = "UPDATE verifysecurityquestionlog SET IsDelete = 1, VerifyResult = concat(VerifyResult,'(reset)') WHERE UserId = @userid AND LastUpdateTime > @periodBeginTime AND IsDelete = 0";
                    var par = this.Parameters.AddInt("@userid", userId).AddDateTime("@periodBeginTime", GetPeriodBeginTime(nextVerifyDayCount)).Build();

                    int rows = conn.Execute(updateSql, par, commandType: CommandType.Text);
                    YmatouLoggingService.Debug("[ResetUserVerifyCount] reset user({0}) security question verify count: {1}", userId, rows);
                }

                res = ResponseData<bool>.CreateSuccess(true, "ok");
            }
            catch(Exception ex)
            {
                YmatouLoggingService.Error(string.Format("[ResetUserVerifyCount] userId: {0}, ex: {1}", userId, ex.Message), ex);
                res = ResponseData<bool>.CreateFail(false, "-400", ex.Message);
            }

            return res;
        }

        /// <summary>
        /// 安全问题验证失败，更新最近一个时间周期内的验证日志的 更新时间（LastUpdateTime)
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="userQuestionId"></param>
        /// <param name="answer"></param>
        /// <param name="nextVerifyDayCount"></param>
        /// <returns></returns>
        public ResponseData<bool> VerifyFailHandle(int userId, int userQuestionId, string answer, int nextVerifyDayCount)
        {
            string updateSql = "UPDATE verifysecurityquestionlog SET VerifyResult = concat(VerifyResult,'(update)') WHERE UserId = @userid AND LastUpdateTime > @periodBeginTime AND IsDelete = 0";
            string insertSql = "INSERT INTO verifysecurityquestionlog (UserQuestionId, UserId, VerifyCode, VerifyResult, IsDelete, CreateTime) VALUES (@userquestionid, @userid, @verifycode, @verifyresult, @isdelete, @createtime)";
            var updatePar = this.Parameters.AddInt("@userid", userId).AddDateTime("@periodBeginTime", GetPeriodBeginTime(nextVerifyDayCount)).Build();
            var insertPar = this.Parameters.AddInt("@userquestionid", userQuestionId)
                    .AddInt("@userid", userId)
                    .AddString("@verifycode", VerifySecurityQuestionResponseCode.WrongAnswer)
                    .AddString("@verifyresult", "用户的安全问题答案错误, " + answer)
                    .AddByte("@isdelete", (byte)0)
                    .AddDateTime("@createtime", DateTime.Now).Build();


            //安全问题验证失败，更新最近一个时间周期内的验证日志的 更新时间
            using (var conn = new MySqlConnection(ConfigurationManager.AppSettings["SecurityQuestion_MySQLConnString"]))
            {
                //更新最近周期的日志更新时间
                conn.Execute(updateSql, updatePar, commandType: CommandType.Text);
                YmatouLoggingService.Debug("[VerifyFailHandle] update relate logs, userId: {0}", userId);

                conn.Execute(insertSql, insertPar, commandType: CommandType.Text);
                YmatouLoggingService.Debug("[VerifyFailHandle] insert fail verify log, userId: {0}", userId);
            }

            ResponseData<bool> res = ResponseData<bool>.CreateSuccess(true, "ok");

            return res;
        }
    }
}
